package com.ict.ms.trackPlayBack.service.hive.helper;

import java.sql.*;
import java.util.ArrayList;


public class HiveDBInterface {

	private String db_ip;
	private int db_port;
	private String db_name;
	private String user_name;
	private String user_passwd;
	private String driverName;
	private String url;
	private Connection con = null;
	private boolean isConnected = false;
	
	public HiveDBInterface(String db_name,String db_ip, int db_port, String user_name, String user_passwd) {
		this.db_ip = db_ip;
		this.db_port = db_port;
		this.user_name = user_name;
		this.user_passwd = user_passwd;
		this.db_name=db_name;
	}

	// 若用户忘记关闭数据库，在析构函数中自动关闭
	protected void finalize(){
		closeDB();
	}
	public void closeDB(){
		try {
			if (!con.isClosed()&&con!=null)
				con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
    
    public void connectToDB(String dbType){
    	driverName = "org.apache.hive.jdbc.HiveDriver";
    	url = "jdbc:hive2://" + db_ip + ":" + db_port + "/"+db_name;
//    	url = "jdbc:hive2://" + db_ip + ":" + db_port;
    	
        try {
            // 加载驱动程序
            Class.forName(driverName);
            // getConnection()方法，连接MySQL数据库！！
            // "?rewriteBatchedStatements=true" 若不置为true，则批处理操作不执行
            // "useServerPrepStmts=true" 若不置为true，则预编译无效果
            con = DriverManager.getConnection(url,user_name,user_passwd);

        } catch (ClassNotFoundException e) {
            System.out.println("数据库驱动类异常，无法打开数据库。");
            e.printStackTrace();

        } catch (SQLException e) {
            System.out.println("数据库连接失败，无法打开数据库。");
            e.printStackTrace();

        } finally {
            System.out.println("成功登陆数据库。");
            isConnected = true;
        }

    }
    
    public ResultSet query1(String sql) throws SQLException {
    	if(isConnected) {
        	Statement stmt = con.createStatement();
        	ResultSet res = stmt.executeQuery(sql);
        	return res;
    	}
    	else {
    		System.out.println("not connect to db!");
    		return null;
    	}
    }
    
    public void query2(String sql) throws SQLException {
    	if(isConnected) {
        	Statement stmt = con.createStatement();
        	stmt.executeUpdate(sql);
    	}
    	else {
    		System.out.println("not connect to db!");
    	}
    }
    
    public void quickInsert(String tableName, ArrayList list) throws SQLException {
    	StringBuilder sql = sqlBuilder(tableName, list);
    	Statement stmt = con.createStatement();
    	stmt.executeUpdate(sql.toString());
//    	stmt.executeQuery(sql.toString());
    	
    }
    
    public void printsql(ArrayList<ArrayList<Object>> list) {
    	System.out.println(sqlBuilder("test", list));
    }
    
    private StringBuilder sqlBuilder(String tableName, ArrayList<ArrayList<Object>> list) {
    	int listNum = list.size();
    	int colNum = list.get(0).size();
    	StringBuilder sql = new StringBuilder();
    	sql.append("insert into table ");
    	sql.append(tableName);
    	sql.append(" values ");
    	for(int i = 0; i < listNum - 1; i++){
    		sql.append("(");
    		for(int j = 0; j < colNum - 1; j++){
    			sql.append(String.valueOf(list.get(i).get(j)));
    			sql.append(",");
    		}
    		sql.append(String.valueOf(list.get(i).get(colNum -1)));
    		sql.append("),");
    	}
    	sql.append("(");
    	for(int j = 0; j < colNum - 1; j++) {
    		sql.append(String.valueOf(list.get(listNum -1).get(j)));
			sql.append(",");
		}
		sql.append(String.valueOf(list.get(listNum -1).get(colNum -1)));
    	sql.append(")");
    	return sql;
    }
}
